﻿

Public Class IOPortTypeService

    Private _rti As RefTypeItem

    Public Sub New(ByVal rti As RefTypeItem)
        _rti = rti
    End Sub

    Public Function GetAiGroupList() As IList(Of IOPortTypeItem)
        Dim il As IList(Of IOPortTypeItem)
        il = New List(Of IOPortTypeItem)

        Dim ds As DataSet

        Dim strSql As String
        strSql = String.Format("Select * From tbccIOInfo_Offline where projectID='{0}' and Netid='{1}' and Refid='{2}' and PortType='1' order by SeqPortNo", _rti.ProjectID, _rti.NetId, _rti.RefId)

        ds = TBCC.Utility.SqlHelper.ExecuteDataset(strSql, Nothing)

        For Each r As DataRow In ds.Tables(0).Rows
            Dim ipti As New IOPortTypeItem()
            ipti.ProjectID = r("projectID")
            ipti.NetId = r("Netid")
            ipti.RefId = r("Refid")
            ipti.SeqPortNo = r("SeqPortNo")
            ipti.PortType = r("PortType")
            ipti.PortNo = r("PortNo")
            ipti.PortName = r("PortName")
            ipti.DataType = r("DataType")

            il.Add(ipti)
        Next

        Return il

    End Function

    Public Function GetDiGroupList() As IList(Of IOPortTypeItem)
        Dim il As IList(Of IOPortTypeItem)
        il = New List(Of IOPortTypeItem)

        Dim ds As DataSet

        Dim strSql As String
        strSql = String.Format("Select * From tbccIOInfo_Offline where projectID='{0}' and Netid='{1}' and Refid='{2}' and PortType='2' order by SeqPortNo", _rti.ProjectID, _rti.NetId, _rti.RefId)

        ds = TBCC.Utility.SqlHelper.ExecuteDataset(strSql, Nothing)

        For Each r As DataRow In ds.Tables(0).Rows
            Dim ipti As New IOPortTypeItem()
            ipti.ProjectID = r("projectID")
            ipti.NetId = r("Netid")
            ipti.RefId = r("Refid")
            ipti.SeqPortNo = r("SeqPortNo")
            ipti.PortType = r("PortType")
            ipti.PortNo = r("PortNo")
            ipti.PortName = r("PortName")
            ipti.DataType = r("DataType")

            il.Add(ipti)
        Next

        Return il

    End Function

    Public Function GetDoGroupList() As IList(Of IOPortTypeItem)
        Dim il As IList(Of IOPortTypeItem)
        il = New List(Of IOPortTypeItem)

        Dim ds As DataSet

        Dim strSql As String
        strSql = String.Format("Select * From tbccIOInfo_Offline where projectID='{0}' and Netid='{1}' and Refid='{2}' and PortType='3' order by SeqPortNo", _rti.ProjectID, _rti.NetId, _rti.RefId)

        ds = TBCC.Utility.SqlHelper.ExecuteDataset(strSql, Nothing)

        For Each r As DataRow In ds.Tables(0).Rows
            Dim ipti As New IOPortTypeItem()
            ipti.ProjectID = r("projectID")
            ipti.NetId = r("Netid")
            ipti.RefId = r("Refid")
            ipti.SeqPortNo = r("SeqPortNo")
            ipti.PortType = r("PortType")
            ipti.PortNo = r("PortNo")
            ipti.PortName = r("PortName")
            ipti.DataType = r("DataType")

            il.Add(ipti)
        Next

        Return il

    End Function

    Public Function UpdateIOPort(ByVal ipti As IOPortTypeItem) As Boolean

        Dim strSql As String
        strSql = "Update tbccIOInfo_Offline Set PortNo=?,PortName=? where projectID=? and Netid=? and Refid=? and SeqPortNo=? and PortType=? and DataType=?"

        Dim p(7) As SQLiteParameter
        p(0) = New SQLiteParameter
        p(0).Value = ipti.PortNo

        p(1) = New SQLiteParameter
        p(1).Value = ipti.PortName

        p(2) = New SQLiteParameter
        p(2).Value = ipti.ProjectID

        p(3) = New SQLiteParameter
        p(3).Value = ipti.NetId

        p(4) = New SQLiteParameter
        p(4).Value = ipti.RefId

        p(5) = New SQLiteParameter
        p(5).Value = ipti.SeqPortNo

        p(6) = New SQLiteParameter
        p(6).Value = ipti.PortType

        p(7) = New SQLiteParameter
        p(7).Value = ipti.DataType

        Return SqlHelper.ExecuteNonQuery(strSql, p)

    End Function

    Public Function InsertIOPort(ByVal ipti As IOPortTypeItem) As Boolean

        Dim strSql As String
        strSql = "Insert Into tbccIOInfo_Offline(projectID,Netid,Refid,SeqPortNo,PortType,PortNo,PortName,DataType) Values(?,?,?,?,?,?,?,?)"

        Dim p(7) As SQLiteParameter
        p(0) = New SQLiteParameter
        p(0).Value = ipti.ProjectID

        p(1) = New SQLiteParameter
        p(1).Value = ipti.NetId

        p(2) = New SQLiteParameter
        p(2).Value = ipti.RefId

        p(3) = New SQLiteParameter
        p(3).Value = ipti.SeqPortNo

        p(4) = New SQLiteParameter
        p(4).Value = ipti.PortType

        p(5) = New SQLiteParameter
        p(5).Value = ipti.PortNo

        p(6) = New SQLiteParameter
        p(6).Value = ipti.PortName

        p(7) = New SQLiteParameter
        p(7).Value = ipti.DataType

        Return SqlHelper.ExecuteNonQuery(strSql, p)

    End Function

    Public Function DeleteIOPort(ByVal SeqPortNo As Integer, ByVal PortType As Integer) As Boolean

        Dim strSql As String
        strSql = "Delete From tbccIOInfo_Offline Where projectID=? and Netid=? and Refid=? and SeqPortNo=? and PortType=?"

        Dim p(4) As SQLiteParameter
        p(0) = New SQLiteParameter
        p(0).Value = _rti.ProjectID 'ipti.ProjectID

        p(1) = New SQLiteParameter
        p(1).Value = _rti.NetId 'ipti.NetId

        p(2) = New SQLiteParameter
        p(2).Value = _rti.RefId 'ipti.RefId

        p(3) = New SQLiteParameter
        p(3).Value = SeqPortNo

        p(4) = New SQLiteParameter
        p(4).Value = PortType

        Return SqlHelper.ExecuteNonQuery(strSql, p)

    End Function

    'portType(1: AI; 2: DI; 3: DO)
    Public Function UpdateIOGroupList(ByVal list As IList(Of IOPortTypeItem), ByVal portType As Integer) As Boolean
        Dim strSql As String
        Dim tic As New TBCC.Utility.TranstrationItem

        strSql = String.Format("Delete From tbccIOInfo_Offline Where projectID='{0}' And Netid='{1}' And Refid='{2}' And PortType='{3}'", _
                                _rti.ProjectID, _rti.NetId, _rti.RefId, portType)
        tic.Add(strSql, Nothing)

        Dim index As Integer = 0
        For Each ipti As IOPortTypeItem In list

            strSql = "Insert Into tbccIOInfo_Offline(projectID,Netid,Refid,SeqPortNo,PortType,PortNo,PortName,DataType) Values(?,?,?,?,?,?,?,?) -- " & index

            Dim p(7) As SQLiteParameter
            For i As Integer = 0 To p.GetUpperBound(0)
                p(i) = New SQLiteParameter
            Next

            p(0).Value = ipti.ProjectID
            p(1).Value = ipti.NetId
            p(2).Value = ipti.RefId
            p(3).Value = ipti.SeqPortNo
            p(4).Value = portType 'ipti.PortType
            p(5).Value = ipti.PortNo
            p(6).Value = ipti.PortName
            p(7).Value = ipti.DataType

            index += 1
            tic.Add(strSql, p)
        Next

        Return SqlHelper.ExecuteTranstration(tic)
    End Function

End Class
